Remote Database Guide

Introduction

TEM is designed to be easy to install and setup when an SQL database is run locally on the Main TEM Server computer. For reasons of cost, performance, maintenance, and control some companies desire the SQL database used by TEM to be located on a separate computer. The following guide outlines the additional configuration steps needed for separating the database from the TEM Server computer. The guide can be read from start to finish to perform a remote database installation from scratch. Alternatively, the steps outlined in this guide can be used to migrate an existing TEM Server installation to a remote database installation.

There are advantages and disadvantages to creating a remote database connection with TEM. The advantages usually include cost savings on SQL licensing, use of existing hardware, access to high performance hardware, consolidation of SQL databases and compliance with existing company policies.

The disadvantages of a remote database connection include added complexity to the installation of the TEM Server, possible performance loss and additional support. Using a remote database installation introduces complexity that could impact the performance and stability of a TEM Server installation. Please ensure that there is a high speed connection from the TEM Server to the database computer (1 Gig/sec minimum), the TEM database will not compete with another application for database resources, the TEM databases are put onto high performance disks and the SQL Server computer has sufficient memory. In addition, there are many manual configuration steps required to make the remote connection and creating them adds support time to setting up the TEM Server. You should expect installing the TEM Server to take an additional thirty minutes to two hours. Finally, upgrades are likely to reset most of the remote database configuration settings and you will need to redo them on upgrades to TEM.

Audience

This guide is intended to be used by the TEM Administrator to create a remote database installation. The TEM Administrator is assumed to have working knowledge of TEM and its components. You should be comfortable with the regular TEM Server installation paths before attempting a remote database installation. Due to the technical aspects of a remote database installation you should also have detailed knowledge of Windows ODBC, Windows Domains and SQL Server. An IT professional with working knowledge of SQL Server should be able to make all the configurations outlined in this guide.

Prerequisites

1. A Windows 2003/2008 computer to function as the main TEM Server.

2. A computer running SQL Server to function as the database computer. MSDE is not supported for remote database configurations.

3. A production license for TEM.

4. A copy of the TEM Server installer folder.

5. Please read through this document entirely before starting the installation.

Initial Setup

1. Install the TEM Server, TEM Client and TEM Console components on the TEM Server computer as normal. During the installation you will be warned that no instance of SQL could be found. Please ignore the warnings and do not install MSDE locally.

2. Configure the DSNs for bes_bfenterprise, bes_EnterpriseServer, enterprise_setup and LocalBESReportingServer to use the database computer as their server.

3. In the TEM Server installer folder is a file named ‘data1.cab’. Double-click on the data1.cab file to display a list of files contained inside. Copy the files ‘BESAdmin.exe’ and ‘initializebesreportsdb.exe’ to a convenient location on the Main TEM Server; you will call them from the command line later to create the database instances.

A default installation puts these files in the following locations:

C:\Program Files (x86)\BigFix Enterprise\BES Server\BESReportsData\initializebesreportsdb.exe

C:\Program Files (x86)\BigFix Enterprise\BES Server\BESAdmin.exe

4. Create two SQL accounts through the Enterprise Manager tool on the SQL database:

One account will be used for NT authentication to the database and the other for SQL authentication. If you plan on only using NT or SQL you will only need to create a single account. The account created for NT authentication has special requirements. Services on the TEM Server must be able to run as the same account used for NT authentication. This means that the SQL Server account must also be a member of the Windows Domain and it must be possible to run services on the Main TEM Server as this user. The account must be an administrator on the Main BES Server as well; it will need to edit the registry and many files.

5. The two SQL accounts must be granted SA privileges on the SQL database. Once the databases have been created, the two SQL accounts only need DBOwner privileges on the BFEnterprise and BESReporting databases. SA privileges are only needed to create the database instances.

6. Test your users!

Create a new DSN on the TEM Server computer and set the DSN to connect to the database computer. On the second page of the Windows ODBC wizard you can try using both NT and SQL authentication to establish a connection to the database. Be sure to check the box in the bottom portion of the screen to test. If you can make it to the next screen in the Wizard then you have successfully created the SQL accounts. NT authentication will attempt to use the user currently logged into Windows so you will need to be logged into Windows as the NT account user

Creating the Database Instances

The databases are created using the BESAdmin.exe and initializebesreportsdb.exe scripts. Run them on the BES Server computer. The sample command line calls below will need to have any expressions contained by <> replaced with the appropriate information.

The BFEnterprise Database

Create the BFEnterprise database by running BESAdmin.exe from the command line.

SQL Authentication

> BESAdmin.exe /installerui /dsn:bes_EnterpriseServer /username:<SQLAccountName> /password:<SQLAccountPassword>

NT Authentication

> runas /user:<mydomain>\<NTAccountName> “BESAdmin.exe /installerui /dsn:enterprise_setup”

At this point you should be prompted for your action site masthead, site level credentials and then asked to create a TEM Console user. Create at least one TEM Console user and click ‘Done’ when finished.

Verification

Verify that the BFEnterprise database was created through Enterprise Manager and that a SQL account appears for the TEM Console user that was created. You may need to close and reopen Enterprise Manager to get the database list to refresh.

The BESReporting Database

Create the BESReporting database by running initializebesreportsdb.exe from the command line. The format for the command looks like this:

InitializeBESReportsDB.exe dbname dsn url [sql_username] [sql_password]

Example values:

• dbname = BESReporting

• dsn = LocalBESReportingServer

• url = http://hostname.hostdomain.tld/webreports

SQL Authentication

>initializebesreportsdb.exe BESReporting LocalBESReportingServer <URLForWebReports> <SQLAccountName> <SQLAccountPassword>

NT Authentication

> runas /user:<mydomain>\<NTAccountName> “initializebesreportsdb.exe BESReporting LocalBESReportingServer <URLForWebReports>

You may see error message about setting permissions in the standard out, these can be safely ignored.

The <URLForWebReports> parameter is the link that will appear in TEM Consoles by default. This value can be changed later if needed so entering an incorrect value at the point will not cause problems. If you try to hit the Web Reports url at this point you will receive the expected error “HTTP 500 – Internal server error”. Information on changing the url post installation is available at http://support.bigfix.com/cgi-bin/kbdirect.pl?id=228.

Sample url: http://mybesserver/cgi-bin/besreports/besreports.exe

Verification

Verify that the BESReporting database was created through Enterprise Manger. You may need to close a reopen Enterprise Manager to get the database list to refresh.

Through Enterprise Manager, change privileges on the NT and SQL accounts to no longer be SA and instead have DBOwner privileges on the BFEnterprise and BESReporting databases. Also, set the SQL account user to use the BFEnterprise database as the default database.

Configure the Main BES Server

There are six components of TEM that need to be configured to connect to the newly created databases: TEM FillDB Service, TEM GatherDB Service, TEM Consoles, TEM Web Reports Server Service, the Web Reports Application and the TEM Administration Tool.

TEM FillDB Service

The TEM FillDB Service is responsible for putting property information from TEM Clients into the database. If this component is broken no computers will appear in the TEM Consoles.

SQL Authentication

Set the following registry keys on the TEM Server.

[HKEY_LOCAL_MACHINE\SOFTWARE\BigFix\Enterprise Server\Database]

String Value: “DSN” = “bes_EnterpriseServer”

[HKEY_LOCAL_MACHINE\SOFTWARE\BigFix\Enterprise Server\Database]

String Value: “User” = “<SQL Account Username>”

[HKEY_LOCAL_MACHINE\SOFTWARE\BigFix\Enterprise Server\Database]

String Value: “Password” = “<SQL Account Password>”

Restart the TEM FillDB service.

Here is a sample registry:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\BigFix\Enterprise Server\Database]

"DSN"="bes_EnterpriseServer"

"User"="<sql account>"

"Password"="<password>"

NT Authentication

Set the BES FillDB service to ‘log on as’ the NT Account user and restart the service.

Verification

The TEM FillDB service keeps a log on the TEM Server found here by default:

C:\Program Files\BigFix Enterprise\BES Server\FillDBData\FillDB.txt

If the service is unable to connect to the database you will see error messages in the log.

Sample error message:

04/28/05 18:01:14 -- Unable to connect to database: ODBC Exception: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

You should also see lots of small files in the BufferDir folder if you have a TEM Client installed.

Once you have connected successfully, the BufferDir folder will empty out and you will see this message in the log:

04/28/05 18:16:44 -- Connecting to database with DSN:bes_bfenterprise Username:

04/28/05 18:16:44 -- Connected to database.

Note: If no one has logged into theTEM Console, you will see the following error message in the FillDB log. Seeing this error indicates that FillDB can reach the database successfully but the database is not initialized yet.

04/28/05 18:48:38 -- Exception encountered in database loop: Database error: The action site has not been initialized.

04/28/05 18:48:38 -- Disconnecting from database.

TEM GatherDB Service

The TEM GatherDB service is responsible for putting Fixlet content into the database. If this component is broken no Fixlets will appear in the TEM Consoles.

SQL Authentication

Set the following registry keys on the TEM Server.

[HKEY_LOCAL_MACHINE\SOFTWARE\BigFix\Enterprise Server\Database]

String Value: “DSN” = “bes_EnterpriseServer”

[HKEY_LOCAL_MACHINE\SOFTWARE\BigFix\Enterprise Server\User]

String Value: “User” = “<SQL Account Username>”

[HKEY_LOCAL_MACHINE\SOFTWARE\BigFix\Enterprise Server\Password]

String Value: “Password” = “<SQL Account Password>”

Restart the TEM GatherDB service.

NT Authentication

Set the TEM GatherDB service to ‘log on as’ the NT Account user and restart the service.

Verification

The TEM GatherDB service keeps a log on the TEM Server found here by default:

C:\Program Files\BigFix Enterprise\BES Server\GatherDBData\GatherDB.txt

If the service is unable to connect to the database you will see error messages in the log.

Sample error message:

04/28/05 18:39:20 -- Unable to connect to database: Database error: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

04/28/05 18:39:20 -- ERROR: Failed to connect to database. Will try again in 10 seconds.

Once you have connected successfully no error message with appear in the log file. GatherDB only writes errors to this log so once the database connection is established no further error messages will appear. Double check the time stamps! If there has not been an error in the past 10 seconds the connection was successful.

Note: If no one has logged into the TEM Console, you will see the following error message in GatherDB log. Seeing this error indicates that GatherDB can reach the database successfully but the database is not initialized yet.

04/28/05 18:45:07 -- Unable to connect to database: ContentVersion is not 'Enterprise 1.50'

04/28/05 18:45:07 -- ERROR: Failed to connect to database. Will try again in 10 seconds.

TEM Consoles

TEM Consoles create a database connection to pull out information and display it to TEM Console Users. The TEM Console installer will create a single DSN: bes_EnterpriseServer. You should not modify the bes_EnterpriseServer DSN on the TEM Server itself. Only modify this DSN on TEM Console computers.

SQL Authentication

Configure the bes_EnterpriseServer DSN to use the database computer as its SQL Server.

NT Authentication

Configure the bes_EnterpriseServer DSN to use the database computer as its SQL Server and set it to use NT Authentication instead of SQL Authentication. You will also need to give your Windows users logins to the SQL database. They will need permission to the BFEnterprise database with equivalent permissions to their TEM Console User’s SQL account and should use the BFEnterprise database as their default database. It may be easier to grant the new users DBOwner privileges then to try to replicate the permissions of the SQL accounts. All TEM Console Users have the same privileges.

Verification

You are prompted for your signing keys when you login to the TEM Console and can load up the TEM Console.

TEM Web Reports Server Service

The TEM Web Reports Server service is responsible for handling Web Reports user communication with the database. The service puts data into and pulls data out of the BESReporting database. If this component is broken you will not be able to load the Web Reports login page.

SQL Authentication

Set the following registry keys on the TEM Server:

[HKEY_LOCAL_MACHINE\SOFTWARE\BigFix\Enterprise Server\FillAggregateDB]

String Value: “Username” = “<SQL Account Username>”

[HKEY_LOCAL_MACHINE\SOFTWARE\BigFix\Enterprise Server\FillAggregateDB]

String Value: “Password” = “<SQL Account Password>”

Configure the LocalBESReportingServer DSN to use SQL authentication instead of NT authentication and set its default database to be the BESReporting database.

Restart the TEM Web Reports Server service.

NT Authentication

Configure the TEM Web Reports Server service to ‘log on as’ the NT Account user and restart the service.

Verification

If this component is working correctly you will be able to view the Web Reports login page. Fixlet data may or may not be viewable once logged into Web Reports even if this component is working. If no data is loaded, there is a problem with component number 5, the Web Reports Application.

If this component is not working correctly you will see the error ‘HTTP 500 – Internal server error’ or an ODBC Exception error when attempting to reach the Web Reports login page.

Example:

ODBC Exception: [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.

Check the LocalBESReportingServer system DSN and restart the Web Reports Server service.

Web Reports Application

The Web Reports application uses one or more database connections to pull data out of the BFEnterprise database and create a cache of the information. The settings for the application are controlled through the ‘settings’ section of Web Reports so you will need to be able to load the application to make the configuration changes. If you are unable to login to Web Reports please refer back to component 4.

SQL Authentication

Click the ‘Edit’ link to go to the database settings menu.

Select the radio button to ‘Use a system DSN’. Enter the ‘DSN Name’ bes_EnterpriseServer.

Select the radio button to ‘Use Username and Password to login’. Provide the SQL Account’s username and password.

Click the ‘Submit’ button to save the settings.

NT Authentication

Click the ‘Edit’ link to go to the database settings menu.

Select the radio button to ‘Use a system DSN’. Enter the ‘DSN Name’ bes_bfenterprise.

Select the radio button to ‘Use Windows Authentication’.

Click the ‘Submit button to save the settings.

Configure the TEM Web Reports Server service to ‘log on as’ the NT Account user and restart the service.

Note: You may need to grant the NT Account user full control of the Enterprise Server registry key if the user does not already have permission. The NT Account user should already be an Administrator on the Main TEM Server but the administrators group may not have permissions set on the TEM Server registry branch.

Verification

If the settings are not correct you will see a red error bar on the Overview section of Web Reports with a message like:

Errors:

- Cannot connect to <TEM Server Name>: Database error: Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. .

Proceed to the Settings page.

If this component is working correctly the overview page will contain information about Fixlets or have a yellow message bar indicating caching is in process. This bar should disappear shortly.

If you click on the ‘Validate’ link for the database settings you can test your configurations.

The TEM Administration Tool

The TEM Admin Tool is used to create new TEM Console users. You will not be able to reach the dialog to create new TEM Console users if this component is not working.

SQL Authentication

> BESAdmin.exe /dsn:bes_EnterpriseServer /username:<SQLAccountName> /password:<SQLAccountPassword>

NT Authentication

> runas /user:<mydomain>\<NTAccountName> “BESAdmin.exe /dsn:bes_bfenterprise”

Verification

If you are prompted for your site level credentials you have successfully connected to the database. Otherwise, you will be taken to a DSN dialog that you can try to use to connect to the database. Using the dialog is often troublesome because it restricts SQL authentication to the SA database user and only allows the currently logged-in Windows user for NT authentication.

Troubleshooting and Extra Information

Question: I am not able to gain even temporary SA permissions on the SQL database, is it possible to create the database without SA privileges?

Answer: You will need SA privileges on some SQL Server to create the TEM database instances. A possible way to work around this problem would be to create the TEM database instances on a separate instance of SQL Server or MSDE that you do have SA privileges to and then detach the database instances. A database administrator with SA privileges would then be able to import the database instances onto the desired SQL Server installation.

Question: Is it possible to use MSDE instead of SQL Server for remote database configurations?

Answer: Yes, it is possible to use MSDE instead of SQL Server. Configuring a remote database setup is probably difficult enough without the restrictions of MSDE so it is not a recommended installation path. Lots of the steps outlined in this guide may not work for MSDE and remote database connections with MSDE have not been well tested. SQL Tools can be installed and used on a MSDE installation and will likely be needed to make the appropriate SQL configurations.

Question: Will I have to make any additional configurations after the initial setup of the remote database?

Answer: Yes, upgrading the TEM Server and TEM Consoles will likely reset many of the configurations you made initially. Please manually upgrade the TEM Server and recheck all the configurations post upgrade. If one of the databases fails to upgrade you will receive error messages stating the database version is too low.

Question: Are there any security risks introduced by using SQL authentication for the remote database configurations?

Answer: Your SQL account’s username and password are stored in plain text in the registry of the TEM Server.

Question: Should I use NT authentication only, SQL authentication only or some combination of both?

Answer: This guide gives you the option to choose NT or SQL authentication for configuring any component. A remote database setup can be created using only NT authentication, only SQL authentication or some combination of the two. It is up to the organization implementing the setup to decide what is best for the situation. Some configurations are dependent on each other so if you choose to implement one in NT then the other must be NT as well and similarly for SQL. For example, the two Web Reports components both must use NT or both must use SQL.

Question: How do I troubleshoot ODBC errors for ‘Invalid object name <some database table>’?

Answer: These errors indicate you have successfully authenticated to the SQL database but made a query against the wrong database instance. Try changing the ‘default database’ for the DSN in question to BFEnterprise, BESReporting or master.

Question: I want to use IIS to host Web Reports instead of the TEM Root Server, is it possible to use IIS with a remote database installation?

Answer: Yes, it is possible to use IIS with a remote database installation.

Please use the following steps in place of step 4 of the Configure the Main TEM Server section:

SQL Authentication

Set the following registry keys on the TEM Server:

[HKEY_LOCAL_MACHINE\SOFTWARE\BigFix\Enterprise Server\FillAggregateDB]

String Value: “Username” = “<SQL Account Username>”

[HKEY_LOCAL_MACHINE\SOFTWARE\BigFix\Enterprise Server\FillAggregateDB]

String Value: “Password” = “<SQL Account Password>”

Configure the LocalBESReportingServer DSN to use SQL authentication instead of NT authentication and set its default database to be the BESReporting database.

Restart IIS.

NT Authentication

There are two possible ways to do NT authentication. IIS will be running as the IUSR of the computer hosting Web Reports by default and will be used for NT authentication to the database. You can either grant the IUSR access to the database or configure IIS to run from the context of the NT Account user that already has permission to the database.

Permissions for the IUSR:

The IUSR can be granted the appropriate permissions when the BESReporting database is created with the initializebesreportingdb.exe script by providing the IUSR as the third parameter to the script.

> initializebesreportsdb.exe bes_EnterpriseServer <URLForWebReports> <WebReportsServerName\IUSR> <SQLAccountName> <SQLAccountPassword>

As an alternative if the database is already created you can grant the IUSR DBOwner permissions on the BESReporting database, read permissions on the BFEnterprise database and update/insert permissions on the AGGREGATEDBY table of the BFEnterprise database.

Configuring IIS for another user:

Follow the steps outlined by this Windows Knowledge Base article to setup IIS to run from the context of the NT Account user.

http://support.microsoft.com/default.aspx?scid=kb;en-us;247931

If you receive an unexpected exception on the overview page of Web Reports and the page never finishes the initial caching phase you may need to create the following registry key and give the IWAM user full control of the folder specified:

[HKEY_LOCAL_MACHINE\SOFTWARE\BigFix\Enterprise Server\BESReports\Paths]

String Value: "sites"="<Full path to a folder for temporary files>"

Question: How do I upgrade a remote database installation from BES/TEM 5.0 to BES/TEM 5.1?

Answer: There are several components of TEM that will each be upgraded seperately. The TEM Installation Generator should be upgraded first, then the TEM Server and TEM Consoles and finally the TEM Relays and TEM Clients. Only the TEM Server and TEM Console upgrades require special instructions for upgrading with a remote database installation.

Upgrading TEM Consoles:

1. Upgrade the TEM Console software normally.

2. The DSNs used by the BES Console will be reset to point to the Main BES Server computer. Reset them to point to the database computer instead.

Upgrading The Main BES Server

1. Run the TEM Server upgrade manually on the Main TEM Server computer, do not use the fixlets for upgradingthe TEM Server.

2. The upgrade will fail when TEM Admin attempts to connect to the database to upgrade the database instances. Click OK to any failure messages. The upgrade will complete without upgrading the database instances to 5.1. The TEM Server software will have upgraded to 5.1 but will now be temporarly incompatible with the 5.0 database instances.

3. Grant the SQL user that you will use to upgrade the database instances SA permissions on the bfenterprise and BESReporting databases. SA permissions are needed to create clustered indexes on the database and a maintenance task to reindex the clustered indexes every six hours.

4. All the DSNs on the Main TEM Server will be reset to point to the local computer. Reset them to point to the database computer instead.

5. Run the BESAdmin.exe and initializebesreportsdb.exe scrpts as outlined in the ‘Creating the Database Instances’ section of this guide to upgrade the database instances. If the databases already exist the scripts will upgrade the database instances instead of creating them. BESAdmin.exe should be run with the switch ‘/upgrade’ instead of ‘/installerui’ for upgrading. The initializebesreportsdb.exe script will upgrade the database without any additional or modified switches.

6. Reset any of the TEM services that were configured to run as a particular user for NT authentication.

7. Restore any registry modifications made for SQL authentication.

8. Test every component of the Main TEM Server to verify that it is working correctly. Verify that the database instances are at the correct versions. Running the SQL query ‘select * from DBINFO’ against the bfenterprise and BESReporting databases will return the current version. BFEnterprise should be at version 1.56 and BESReporting at version 7 for BES 5.1.

9. Restore SQL permissions to the SQL user modified in step 3.